package com.rykj.qxj.server.services.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.rykj.qxj.server.common.utils.CollectionUtils;
import com.rykj.qxj.server.dao.MetadataBasicMapper;
import com.rykj.qxj.server.dao.MetadataElementMapper;
import com.rykj.qxj.server.model.entity.MetadataBasic;
import com.rykj.qxj.server.model.entity.MetadataElement;
import com.rykj.qxj.server.model.param.AuditParams;
import com.rykj.qxj.server.model.param.DeleteFieldParams;
import com.rykj.qxj.server.model.param.MetadataParams;
import com.rykj.qxj.server.services.IMetadataManageService;
import com.rykj.qxj.server.util.DES;
import com.rykj.qxj.server.util.object.StringUtils;
import lombok.extern.log4j.Log4j;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.*;
import java.util.*;

@Service("metadataManageService")
@Log4j
public class MetadataManageServiceImpl implements IMetadataManageService {

    @Resource
    private MetadataBasicMapper basicMapper;

    @Resource
    private MetadataElementMapper elementMapper;

    private final   String mysqlDriver = "com.mysql.jdbc.Driver";

    @Override
    public String saveMetadataBasic(MetadataParams  metadataParams) throws Exception{
        String createdTableSql ="";
        String errorMsg="";
        MetadataBasic metadataBasic = metadataParams.getMetadataBasic();
        String tableBuildStatement = metadataBasic.getTableBuildStatement();
        List<MetadataElement> metadataElements = metadataParams.getMetadataElements();
        String currentTableName=metadataBasic.getTableName();
        CreateTable createTable =null;
        try {
            if(StringUtils.isNotEmpty(tableBuildStatement)){
                log.info("建表语句是:===="+tableBuildStatement);
                createTable = (CreateTable) CCJSqlParserUtil.parse(tableBuildStatement);
                currentTableName = createTable.getTable().getName().replaceAll("`","");
            }
        }catch (Exception e) {
            log.error("建表语句异常",e);
            throw new Exception("建表语句语法错误");
        }
       // String currentTableName=createTable.getTable().getName().replaceAll("`","");
        Integer metadataId = checkTableName(metadataBasic.getTableName(), metadataBasic.getBelongDatabase());
        if (metadataId != null) {
            return  "表名已存在";
        }
        String sql="select table_name from information_schema.tables where table_schema=?";
        log.info("你目前操作的数据库是:=="+metadataParams.getDatabaseName());
        Set<String> tableSets = selectAllTablesByexecuteSql(metadataParams,sql);
        log.info("你创建表的名称是:=="+currentTableName);
        if("subject".equals(metadataParams.getMetadataBasic().getClassification())) {
        	//主题库的操作
        	if(tableSets.contains(currentTableName.toLowerCase())) {
        		//如果数据采集子系统中表存在
        		//metadataBasic.setTableName(currentTableName);
        		 metadataElements = getElement(metadataBasic);
        		basicMapper.saveMetadataBasic(metadataBasic);
                if (!CollectionUtils.isEmpty(metadataElements)) {
                    metadataElements.forEach(e->{
                        e.setTableId(metadataBasic.getId());
                    });
                    elementMapper.saveMetadataElement(metadataElements);
                }
        	}else {
        		//表不存在
        		 if(StringUtils.isNotEmpty(tableBuildStatement)){
        	        	//sql方式建表的方式
        	            metadataElements = getElement(metadataBasic);
        	            createdTableSql =tableBuildStatement;
        	        }else {
        	            //可视化创建表
        	            createdTableSql = getCreatTablesql(metadataParams);
        	        }
        		    log.info("====建表语句===="+createdTableSql);
        	        if (StringUtils.isNotEmpty(createdTableSql)) {
        	            List<String> createdTableSqls= new ArrayList<>();
        	            createdTableSqls.add(createdTableSql);
        	            errorMsg =   executeDatabaseStatements(metadataParams,createdTableSqls);
        	        }
        	        if(StringUtils.isEmpty(errorMsg)){
        	            basicMapper.saveMetadataBasic(metadataBasic);
        	            if (!CollectionUtils.isEmpty(metadataElements)) {
        	                metadataElements.forEach(e->{
        	                    e.setTableId(metadataBasic.getId());
        	                });
        	                elementMapper.saveMetadataElement(metadataElements);
        	            }
        	        }
        	}
        }else {
        	//业务库的处理逻辑
            if(StringUtils.isNotEmpty(tableBuildStatement)){
                //sql方式建表的方式
                metadataElements = getElement(metadataBasic);
                createdTableSql =tableBuildStatement;
            }else {
                //可视化创建表
                createdTableSql = getCreatTablesql(metadataParams);
            }

            log.info("====建表语句===="+createdTableSql);
            if(!tableSets.contains(currentTableName)){
                if (StringUtils.isNotEmpty(createdTableSql)) {
                    List<String> createdTableSqls= new ArrayList<>();
                    createdTableSqls.add(createdTableSql);
                    errorMsg =   executeDatabaseStatements(metadataParams,createdTableSqls);
                }
            }
            if(StringUtils.isEmpty(errorMsg)){
                basicMapper.saveMetadataBasic(metadataBasic);
                if (!CollectionUtils.isEmpty(metadataElements)) {
                    metadataElements.forEach(e->{
                        e.setTableId(metadataBasic.getId());
                    });
                    elementMapper.saveMetadataElement(metadataElements);
                }
            }
       }
        return errorMsg;
    }

    @Override
    public String updateMetadataBasic(MetadataParams metadataParams) throws Exception{
        String errorMsg="";

        MetadataBasic  metadataBasic = metadataParams.getMetadataBasic();
//        MetadataBasic oldMetadataBasic = basicMapper.getMetadataBasicId(metadataBasic.getId());
//        List<String> extSqls = new ArrayList<>();
//        if(!oldMetadataBasic.getTableName().equals(metadataBasic.getTableName())){
//            extSqls.add(" ALTER TABLE  `"+oldMetadataBasic.getTableName()+"`   RENAME  `"+metadataBasic.getTableName()+"`  ;");
//        }
//        if(!oldMetadataBasic.getTableNotes().equals(metadataBasic.getTableNotes())){
//            extSqls.add(" ALTER TABLE `"+metadataBasic.getTableName()+"` comment '"+metadataBasic.getTableNotes()+"';");
//        }
//        if(CollectionUtils.isNotEmpty(extSqls)){
//            errorMsg =  executeDatabaseStatements(metadataParams,extSqls);
//            if(StringUtils.isEmpty(errorMsg)){
                basicMapper.updateMetadataBasic(metadataBasic);
//            }
//        }
        return errorMsg;

    }

    @Override
    public String deleteMetadataBasicById(Integer metadataBasicId) throws Exception {
       // MetadataBasic metadataBasic=  metadataParams.getMetadataBasic();
        Map<String,Object>  metadata  = basicMapper.getMetadataInfoById(metadataBasicId);
        MetadataParams  metadataParams = new MetadataParams();
        String  databaseIP = (String) metadata.get("databaseIP");
        String  port = (String) metadata.get("port");
        String userName = (String) metadata.get("userName");
        String password = (String) metadata.get("password");
        String databaseName = (String) metadata.get("databaseName");
        String tableName = (String) metadata.get("tableName");
        metadataParams.setDatabaseIP(databaseIP);
        metadataParams.setPort(port);
        metadataParams.setUserName(userName);
        metadataParams.setPassword(password);
        metadataParams.setDatabaseName(databaseName);
        metadataParams.setTableName(tableName);
        String dropTableSql= " DROP table `"+tableName+"`";
        List<String> dropTableSqls =new ArrayList<>();
        dropTableSqls.add(dropTableSql);
        String errorMsg =  executeDatabaseStatements(metadataParams,dropTableSqls);
        if(StringUtils.isEmpty(errorMsg)){
            basicMapper.deleteMetadataBasicById(metadataBasicId);
        }
        return  errorMsg;
    }

    @Override
    public String saveMetadataElement(MetadataParams  metadataParams ) throws Exception{
        List<MetadataElement> metadataElements = metadataParams.getMetadataElements();
        String errorMsg = checkElementRepeat(metadataElements);
        if(StringUtils.isNotEmpty(errorMsg)){
            return errorMsg;
        }
        Map<String,Object> paraMap = new HashMap<>();
        for (MetadataElement element:metadataElements) {
            paraMap.put("tableId",element.getTableId());
            paraMap.put("fieldName",element.getFieldName());
            MetadataElement checkElement = elementMapper.checkElement(paraMap);
            if(checkElement!=null){
                return "字段"+checkElement.getFieldName()+"已存在";
            }
        }
        List<String> fieldSals = getCreateFieldSql(metadataParams.getMetadataBasic().getTableName(),metadataElements);
        elementMapper.saveMetadataElement(metadataElements);
       // elementMapper.saveSignMetadataElement(metadataElements.get(0));
        return executeDatabaseStatements(metadataParams,fieldSals);
    }

    @Override
    public String updateMetadataElement(MetadataParams metadataParams) throws Exception{
        List<MetadataElement> metadataElements = metadataParams.getMetadataElements();
        String errorMsg ="";
        errorMsg = checkElementRepeat(metadataElements);
        if(StringUtils.isNotEmpty(errorMsg)){
            return errorMsg;
        }
        Map<String,Object> paraMap = new HashMap<>();
        List<String> extSqls =new ArrayList<>();
        for (MetadataElement element:metadataElements ) {
            if(element.getFieldLength()<element.getFieldAccuracy()){
                throw new Exception("精度不能大于长度");
            }
            paraMap.put("tableId",element.getTableId());
            paraMap.put("fieldName",element.getFieldName());
            MetadataElement checkElement = elementMapper.checkElement(paraMap);
            if(checkElement !=null && checkElement.getId()!=element.getId()){
                if(checkElement!=null){
                    return "字段"+checkElement.getFieldName()+"已存在";
                }
            }

            MetadataElement oldMetadataElement = elementMapper.getMetadataElementByFieldId(element.getId());
           // MetadataElement metadataElement = metadataElements.get(0);
            StringBuilder extSql = new StringBuilder();
            extSql.append("ALTER TABLE " ) ;
            extSql.append(" `"+metadataParams.getMetadataBasic().getTableName()+"` ");
            extSql.append(" CHANGE `"+oldMetadataElement.getFieldName()+"`  `"+element.getFieldName()+"` " );
            extSql.append(" " +element.getFieldType()) ;
            if (element.getFieldLength()>0){
                if("date".equals(element.getFieldType())||"datetime".equals(element.getFieldType())){
                    throw new Exception("日期类型不需要长度");
                }
                extSql.append( " ("+element.getFieldLength());
                 if("double".equals(element.getFieldType())||(element.getFieldAccuracy()>0 && !"int".equals(element.getFieldType()))){
                     extSql.append(","+element.getFieldAccuracy());
                 }
                extSql.append(") ");
            }else{
                int legth=getTypelength(element.getFieldType());
                if(legth>0){
                    extSql.append("("+legth );
                    if("double".equals(element.getFieldType())){
                        extSql.append(","+element.getFieldAccuracy());
                    }
                    extSql.append(") ");
                }
            }
            if("Y".equals(element.getIsEmpty())|| StringUtils.isEmpty(element.getIsEmpty())){
                extSql.append(" DEFAULT NULL ");
            }else {
                extSql.append(" NOT NULL ");
            }
            if("N".equals(element.getIsEmpty()) && StringUtils.isNotEmpty(element.getDefaultValue())){
                extSql.append(" DEFAULT '"+element.getDefaultValue()+"' ");
            }
            extSql.append(" COMMENT '" +element.getFieldRemarks() +"' ") ;
            extSqls.add(extSql.toString());
        }

        errorMsg =  executeDatabaseStatements(metadataParams,extSqls);
        if(StringUtils.isEmpty(errorMsg)){
            for (MetadataElement metadataElement:metadataElements ) {
                elementMapper.updateMetadataElement(metadataElement);
            }
        }
        return   errorMsg;
    }

    @Override
    public PageInfo getMetadataBasicList(Integer pageNum, Integer pageSize, String tableName,String tableNotes, String classification, String directory,String belongDatabase,String auditStatus) {
        PageHelper.startPage(pageNum,pageSize);
        Map<String,Object> paraMap = new HashMap<>();
        if("%".equals(tableNotes)){
            tableNotes ="\\%";
        }
        if("%".equals(tableName)){
            tableName ="\\%";
        }
        paraMap.put("tableNotes",tableNotes);
        paraMap.put("tableName",tableName);
        paraMap.put("classification",classification);
        paraMap.put("directory",directory);
        paraMap.put("belongDatabase",belongDatabase);
        paraMap.put("auditStatus",auditStatus);
        List<Map<String,Object>> metadataBasics=  basicMapper.getMetadataBasicList(paraMap);
        PageInfo pageInfo = new PageInfo(metadataBasics);
        return  pageInfo;
    }

    @Override
    public PageInfo getMetadataElementList(Integer pageNum, Integer pageSize, String tableName,Integer tableId) {
        PageHelper.startPage(pageNum,pageSize);
        Map<String,Object> paraMap = new HashMap<>();
        if("%".equals(tableName)){
            tableName ="\\%";
        }
        paraMap.put("tableName",tableName);
        paraMap.put("tableId",tableId);
        List<MetadataElement> metadataElements=  elementMapper.getMetadataElementList(paraMap);
        PageInfo pageInfo = new PageInfo(metadataElements);
        return  pageInfo;

    }

    @Override
    public MetadataBasic getMetadataBasicId(Integer metadataBasicId) {
       return basicMapper.getMetadataBasicId(metadataBasicId);
    }

    @Override
    public String deleteMetadataElementById(DeleteFieldParams deleteFieldParams) throws Exception {
        String errorMsg="";
        MetadataParams metadataParams = new MetadataParams();
        metadataParams.setUserName(deleteFieldParams.getUserName());
        metadataParams.setPassword(deleteFieldParams.getPassword());
        metadataParams.setDatabaseName(deleteFieldParams.getDatabaseName());
        metadataParams.setDatabaseIP(deleteFieldParams.getDatabaseIP());
        metadataParams.setPort(deleteFieldParams.getPort());
        StringBuilder deleteFieldSql = new StringBuilder();
        deleteFieldSql.append(" alter table ");
        deleteFieldSql.append( " `"+deleteFieldParams.getTableName()+"` ");
        deleteFieldSql.append(" DROP ");
        deleteFieldSql.append(" COLUMN ");
        deleteFieldSql.append( " `"+deleteFieldParams.getFieldName()+"` " );
        deleteFieldSql.append(";\n");

        List<String> deleteFieldSqls = new ArrayList<>();
        deleteFieldSqls.add(deleteFieldSql.toString());
        errorMsg =  executeDatabaseStatements(metadataParams,deleteFieldSqls);
        if(StringUtils.isEmpty(errorMsg)){
            elementMapper.deleteMetadataElementById(deleteFieldParams.getFieldId());
        }
        return errorMsg;
    }

    @Override
    public   List<MetadataElement> getElement(MetadataBasic metadataBasic) throws  Exception{
      List<MetadataElement> metadataElements = new ArrayList<>();
      String tableBuildStatement = metadataBasic.getTableBuildStatement();
      if(StringUtils.isEmpty(tableBuildStatement)){
         return metadataElements ;
      }
        CreateTable createTable  =null;
      try {
          createTable = (CreateTable) CCJSqlParserUtil.parse(tableBuildStatement);
      }catch (Exception e) {
            log.error("建表语句异常",e);
          throw new Exception("建表语句语法错误");
      }
      metadataBasic.setTableName(createTable.getTable().getName().replaceAll("`",""));
      List<?> tableOptions = createTable.getTableOptionsStrings();
      metadataBasic.setTableNotes(((String) tableOptions.get(tableOptions.size()-1)).replaceAll("'","").replaceAll("`",""));
     //索引
      List<Index> indexes =createTable.getIndexes();
      //表字段元素
      List<ColumnDefinition>  columnDefinitions= createTable.getColumnDefinitions();
      columnDefinitions.forEach(column->{
          MetadataElement  metadataElement = new MetadataElement();
          metadataElement.setCreatedBy(metadataBasic.getCreatedBy());
          metadataElement.setIsKey("N");
          metadataElement.setIsEmpty("N");
          if(null !=column && CollectionUtils.isNotEmpty(column.getColumnSpecStrings())){
              if ("NULL".equalsIgnoreCase(column.getColumnSpecStrings().get(1))){
                  metadataElement.setIsEmpty("Y");
              }else {
                  metadataElement.setDefaultValue(column.getColumnSpecStrings().get(1));
              }
          }
          metadataElement.setTableId(metadataBasic.getId());
          if(CollectionUtils.isNotEmpty(column.getColumnSpecStrings())){
              List<String> columnSpecStrings = column.getColumnSpecStrings();
              metadataElement.setFieldRemarks(columnSpecStrings.get(column.getColumnSpecStrings().size()-1).replaceAll("`","").replaceAll("'",""));
              //自增
              if(columnSpecStrings.size()>2 && "AUTO_INCREMENT".equals(columnSpecStrings.get(2))){
                  metadataElement.setIsEmpty("N");
                  metadataElement.setIsIncrease("Y");
              }
          }
          if(StringUtils.isNotEmpty(column.getColDataType()) && CollectionUtils.isNotEmpty(column.getColDataType().getArgumentsStringList())){
              metadataElement.setFieldLength(Integer.parseInt(column.getColDataType().getArgumentsStringList().get(0)));
              if(column.getColDataType().getArgumentsStringList().size()==2){
                  metadataElement.setFieldAccuracy(Integer.parseInt(column.getColDataType().getArgumentsStringList().get(1)));
              }
          }
//        metadataElement.setAuditId();
          metadataElement.setIsIncrease("N");
          metadataElement.setFieldName(column.getColumnName().replaceAll("`",""));
          metadataElement.setFieldType(column.getColDataType().getDataType());

          if(indexes!=null){
              if(indexes.get(0).getColumnsNames().get(0).equalsIgnoreCase(column.getColumnName())){
                  metadataElement.setIsKey("Y");
              }
          }
          metadataElements.add(metadataElement);
      });
      return  metadataElements;
    }

    @Override
    public Integer checkTableName(String tableName, String belongDatabase) {
        Map<String,Object> paraMap =new HashMap<>();
        paraMap.put("tableName",tableName);
        paraMap.put("belongDatabase",belongDatabase);
        return  basicMapper.checkTableName(paraMap);
    }

    /**
     * 创建数据库连接
     * @param metadataParams
     */
    @Override
    public Connection createDatabaseConn(MetadataParams metadataParams) throws  Exception{
            //连接数据库名，新建表及字段信息
            String databaseUrl = "jdbc:mysql://"+metadataParams.getDatabaseIP()+":"+metadataParams.getPort()+"/"+metadataParams.getDatabaseName()+ "?useUnicode=true&characterEncoding=utf-8";
            Connection conn = null;
            try {
                Class.forName(mysqlDriver);
            } catch (ClassNotFoundException e) {
                throw  new Exception("未安装mysql驱动连接程序");
            }
            String password = metadataParams.getPassword().replaceAll("%2B","+");
            metadataParams.setPassword(password);
            try {
                log.info("====连接数据库地址====="+databaseUrl);
                DriverManager.setLoginTimeout(3);
                conn = DriverManager.getConnection(databaseUrl ,metadataParams.getUserName(), new DES().decode(metadataParams.getPassword()));
               if(conn== null){
                log.error("创建连接失败");
                return  null;
               }
                System.out.println(metadataParams.getDatabaseIP()+":创建连接数据库成功！");
            } catch (SQLException e) {
                log.error("创建连接超时",e);
                throw  new Exception("创建连接超时");
            }
         return conn;
    }
    //建表语句
    private  String getCreatTablesql(MetadataParams metadataParams) throws Exception {
        MetadataBasic metadataBasic = metadataParams.getMetadataBasic();
        List<MetadataElement> metadataElements =metadataParams.getMetadataElements();
        StringBuilder tableSql= new StringBuilder();
        tableSql.append(" CREATE TABLE `"+metadataBasic.getTableName()+"` (");
        tableSql.append(getCreateFieldSql(metadataElements));
        tableSql.append(" ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='"+metadataBasic.getTableNotes()+"';");
      return  tableSql.toString();
    }

   private String getCreateFieldSql(List<MetadataElement> metadataElements) throws Exception{
       StringBuilder fieldSql= new StringBuilder();
           String  primaryKey = "";
           for (MetadataElement e: metadataElements) {
               if(e.getFieldLength()<e.getFieldAccuracy()){
                   throw new Exception("精度不能大于长度");
               }
                if("Y".equals(e.getIsKey())){
                    primaryKey =  " PRIMARY KEY (`"+e.getFieldName()+"`) ";
                }
               fieldSql.append(" `"+e.getFieldName()+"` ");
               fieldSql.append(" "+ e.getFieldType());

               if (e.getFieldLength()>0){
                   if("date".equals(e.getFieldType()) ||"datetime".equals(e.getFieldType())){
                      e.setFieldLength(0);
                       //throw new Exception("日期类型不需要长度");
                   }
                   fieldSql.append("("+e.getFieldLength());
                   if("double".equals(e.getFieldType())||(e.getFieldAccuracy()>0 && !"int".equals(e.getFieldType()))){
                       fieldSql.append(","+e.getFieldAccuracy());
                   }
                   fieldSql.append(")");
               } else{
                   int legth=getTypelength(e.getFieldType());
                   if(legth>0){
                       if("double".equals(e.getFieldType())){
                           fieldSql.append("("+legth+",0)");
                       }else {
                           fieldSql.append("("+legth+")");
                       }
                   }
               }
               if("Y".equals(e.getIsEmpty())|| StringUtils.isEmpty(e.getIsEmpty())){
                   fieldSql.append(" DEFAULT NULL ");
               }else {
                   fieldSql.append(" NOT NULL ");
               }
               if("N".equals(e.getIsEmpty()) && StringUtils.isNotEmpty(e.getDefaultValue())){
                   fieldSql.append(" DEFAULT '"+e.getDefaultValue()+"' ");
               }
               if("Y".equals(e.getIsIncrease())){
                   fieldSql.append(" AUTO_INCREMENT ");
               }
               fieldSql.append(" COMMENT ");
               fieldSql.append(" '"+e.getFieldRemarks()+"' ");
               fieldSql.append(",\n");
           }
           if(StringUtils.isEmpty(primaryKey)){
               fieldSql = fieldSql.deleteCharAt(fieldSql.lastIndexOf(","));
           }else {
               fieldSql.append(primaryKey);
           }
       return fieldSql.toString();
   }

    //新建字段语句
    private List<String>  getCreateFieldSql(String tableName ,List<MetadataElement> metadataElements) throws Exception{
        List<String> exctSqls = new ArrayList<>();
        if (!CollectionUtils.isEmpty(metadataElements)) {
            for (MetadataElement e:metadataElements) {
                if(e.getFieldLength()<e.getFieldAccuracy()){
                    throw new Exception("精度不能大于长度");
                }
                StringBuilder fieldSql= new StringBuilder();
                fieldSql.append(" alter table ");
                fieldSql.append(" `"+tableName +"` ");
                fieldSql.append(" ADD ");
                fieldSql.append(" `"+e.getFieldName()+"` ");
                fieldSql.append(" "+ e.getFieldType());
                if (e.getFieldLength()>0 ){
                    if("date".equals(e.getFieldType()) ||"datetime".equals(e.getFieldType())){
                        throw new Exception("日期类型不需要长度");
                    }
                    fieldSql.append("("+e.getFieldLength());
                    if("double".equals(e.getFieldType())||(e.getFieldAccuracy()>0 && !"int".equals(e.getFieldType()))){
                        fieldSql.append(","+e.getFieldAccuracy());
                    }
                    fieldSql.append(")");
                }else {
                    int legth=getTypelength(e.getFieldType());
                    if(legth>0){
                        if("double".equals(e.getFieldType())){
                            fieldSql.append("("+legth+",0)");
                        }else {
                            fieldSql.append("("+legth+")");
                        }
                    }
                }
                if("Y".equals(e.getIsEmpty())|| StringUtils.isEmpty(e.getIsEmpty())){
                    fieldSql.append(" DEFAULT NULL ");
                }else {
                    fieldSql.append(" NOT NULL ");
                }
                if("N".equals(e.getIsEmpty()) && StringUtils.isNotEmpty(e.getDefaultValue())){
                    fieldSql.append(" DEFAULT '"+e.getDefaultValue()+"' ");
                }
                fieldSql.append(" COMMENT ");
                fieldSql.append(" '"+e.getFieldRemarks()+"' ");
                fieldSql.append(";\n");
                exctSqls.add(fieldSql.toString());
            }
        }
        return exctSqls;
    }


    //修改字段语句
    private List<String>   getUpdateFieldSql(String tableName , List<MetadataElement> metadataElements)throws Exception {
        List<String>  fieldSqls =new ArrayList<>();
        if(!CollectionUtils.isEmpty(metadataElements)){
            for (MetadataElement metadataElement:metadataElements) {
                if("delete".equals(metadataElement.getStatus())){
                    continue;
                }
                if(metadataElement.getFieldLength()<metadataElement.getFieldAccuracy()){
                    throw new Exception("精度不能大于长度");
                }
                StringBuilder fieldSql= new StringBuilder();
                fieldSql.append(" alter table ");
                fieldSql.append(" `"+tableName+"` ");
                fieldSql.append(" MODIFY  ");
                fieldSql.append(" `"+metadataElement.getFieldName()+"` ");
                fieldSql.append(" " +metadataElement.getFieldType());
                if (metadataElement.getFieldLength()>0){
                    if("date".equals(metadataElement.getFieldType()) ||"datetime".equals(metadataElement.getFieldType())){
                        throw new Exception("日期类型不需要长度");
                    }
                    fieldSql.append("("+metadataElement.getFieldLength());
                    if("double".equals(metadataElement.getFieldType())||(metadataElement.getFieldAccuracy()>0&& !"int".equals(metadataElement.getFieldType()))){
                        fieldSql.append(","+metadataElement.getFieldAccuracy());
                    }
                    fieldSql.append(")");
                }else {
                    int legth=getTypelength(metadataElement.getFieldType());
                    if(legth>0){
                        if("double".equals(metadataElement.getFieldType())){
                            fieldSql.append("("+legth+",0)");
                        }else {
                            fieldSql.append("("+legth+")");
                        }
                    }
                }
                if("Y".equals(metadataElement.getIsEmpty())|| StringUtils.isEmpty(metadataElement.getIsEmpty())){
                    fieldSql.append(" DEFAULT NULL ");
                }else {
                    fieldSql.append(" NOT NULL ");
                }
                if("N".equals(metadataElement.getIsEmpty()) && StringUtils.isNotEmpty(metadataElement.getDefaultValue())){
                    fieldSql.append(" DEFAULT '"+metadataElement.getDefaultValue()+"' ");
                }
                fieldSql.append("  COMMENT ");
                fieldSql.append("' "+metadataElement.getFieldRemarks()+"' ");
                fieldSql.append(";\n");
                fieldSqls.add(fieldSql.toString());
            }
        }
        return fieldSqls;
    }

    //删除字段语句
    private List<String>  getDeleteFieldSql(String tableName ,List<MetadataElement> metadataElements) {
        List<String> fieldSqls =new ArrayList<>();
        StringBuilder fieldSql= new StringBuilder();
        if (!CollectionUtils.isEmpty(metadataElements)) {
            for (MetadataElement metadataElement:metadataElements) {
                if("delete".equals(metadataElement.getStatus())){
                    continue;
                }
                fieldSql.append(" alter table ");
                fieldSql.append(" `"+tableName +"` ");
                fieldSql.append(" DROP ");
                fieldSql.append(" COLUMN ");
                fieldSql.append(" `"+metadataElement.getFieldName()+"` ");
                fieldSql.append(";\n");
                fieldSqls.add(fieldSql.toString());
            }
       }
        return fieldSqls;
    }

    /**
     * 审核通过 新增字段生效操作
     * @param auditParams
     */
    @Override
    public String addAuditFields(AuditParams auditParams) throws Exception {
        MetadataParams metadataParams = new MetadataParams();
        metadataParams.setDatabaseIP(auditParams.getDatabaseIP());
        metadataParams.setDatabaseName(auditParams.getDatabaseName());
        metadataParams.setPassword(auditParams.getPassword());
        metadataParams.setUserName(auditParams.getUserName());
        metadataParams.setPort(auditParams.getPort());
        List<MetadataElement> metadataElements =  elementMapper.getAuditFieldDetails(auditParams.getAuditId());
        //新增字段语句
        List<String> addSql = getCreateFieldSql(auditParams.getTableName(),metadataElements);
        return executeDatabaseStatements(metadataParams,addSql);
    }

    /**
     * 审核通过 修改字段生效操作
     * @param auditParams
     * @return
     */
    @Override
    public String modifyAuditFields(AuditParams auditParams) throws Exception {
        MetadataParams metadataParams = new MetadataParams();
        metadataParams.setDatabaseIP(auditParams.getDatabaseIP());
        metadataParams.setDatabaseName(auditParams.getDatabaseName());
        metadataParams.setPassword(auditParams.getPassword());
        metadataParams.setUserName(auditParams.getUserName());
        metadataParams.setPort(auditParams.getPort());
        List<MetadataElement> metadataElements =  elementMapper.getAuditFieldDetails(auditParams.getAuditId());
        if(CollectionUtils.isEmpty(metadataElements)){
            return "修改的字段已删除";
        }
        List<String> modifySql = getUpdateFieldSql(auditParams.getTableName(),metadataElements);
        return executeDatabaseStatements(metadataParams,modifySql);
    }

    /**
     * 审核通过 删除字段生效操作
     * @param auditParams
     */
    @Override
    public String dropAuditFields(AuditParams auditParams) throws Exception {
        MetadataParams metadataParams = new MetadataParams();
        metadataParams.setDatabaseIP(auditParams.getDatabaseIP());
        metadataParams.setDatabaseName(auditParams.getDatabaseName());
        metadataParams.setPassword(auditParams.getPassword());
        metadataParams.setUserName(auditParams.getUserName());
        metadataParams.setPort(auditParams.getPort());
        List<MetadataElement> metadataElements =  elementMapper.getDropFieldsByAuditId(auditParams.getAuditId());
        List<String> dropSqls = getDeleteFieldSql(auditParams.getTableName(),metadataElements);
        return executeDatabaseStatements(metadataParams,dropSqls);
    }

    /**
     * 执行数据库ddl语句
     * @param metadataParams
     * @param excutSqls
     * @return
     */
    private  String executeDatabaseStatements(MetadataParams metadataParams,List<String> excutSqls) throws Exception {
        if(CollectionUtils.isEmpty(excutSqls)){
            return "未生成ddl执行语句，字段可能不存在";
        }
        Connection conn = null;
        try {
            conn = createDatabaseConn(metadataParams);
            if (conn==null){
                log.error("创建数据库连接失败");
                return "创建数据库连接失败";
            }
            log.info("===执行语句===excutSql===="+excutSqls);
            Statement smt = conn.createStatement();
            for (String excutSql:  excutSqls) {
                smt.executeUpdate(excutSql);// smt.executeUpdate(TableBuildStatement);
            }
        }catch (Exception e){
            log.error("创建连接超时或执行DDL语句异常",e);
            throw new Exception(e.getMessage());
        }finally {
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error("关闭数据库连接失败",e);
                    return "关闭数据库连接失败";
                }
            }
        }
        return "";
    }
    /**
     * 
     * @Description: 根据数据库查询数据库中的所有表 
     * @date:   2019年7月8日 下午12:57:04  
     * @author: wangshunyi    
     * @throws
     */
    private  Set<String> selectAllTablesByexecuteSql(MetadataParams metadataParams,String excutSqls) throws Exception {
    	log.info("开始执行根据数据库查询数据库中的表========**");
    	Connection conn = null;
    	Set<String> resultSets=new HashSet<String>();
    	try {
    		conn = createDatabaseConn(metadataParams);
    		if (conn==null){
    			log.error("创建数据库连接失败");
    		}
    		log.info("===执行语句===excutSql===="+excutSqls);
    		PreparedStatement prepareStatement = conn.prepareStatement(excutSqls);
    		prepareStatement.setString(1, metadataParams.getDatabaseName());
    		ResultSet rs = prepareStatement.executeQuery();
    		while(rs.next()) {
    			resultSets.add(rs.getString("table_name").toLowerCase());
    		}
    		log.info("查询执行完成========="+resultSets.toString());
    	}catch (Exception e){
    		log.error("创建连接超时或执行查询语句异常",e);
    		throw new Exception(e.getMessage());
    	}finally {
    		if(conn!=null){
    			try {
    				conn.close();
    			} catch (SQLException e) {
    				log.error("关闭数据库连接失败",e);
    			}
    		}
    	}
    	return resultSets;
    }
    @Override
    public String createdAuditTable(AuditParams auditParams) throws Exception {
        MetadataParams metadataParams = new MetadataParams();
        metadataParams.setDatabaseIP(auditParams.getDatabaseIP());
        metadataParams.setDatabaseName(auditParams.getDatabaseName());
        metadataParams.setPassword(auditParams.getPassword());
        metadataParams.setUserName(auditParams.getUserName());
        metadataParams.setPort(auditParams.getPort());
        List<MetadataElement> metadataElements =  elementMapper.getAuditFieldDetails(auditParams.getAuditId());
        StringBuilder tableSql= new StringBuilder();
        tableSql.append(" CREATE TABLE `"+auditParams.getTableName()+"` (");
        tableSql.append(getCreateFieldSql(metadataElements));
        tableSql.append(" ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='"+auditParams.getTableNotes()+"';");
        List<String> tableSqls = new ArrayList<>();
        tableSqls.add(tableSql.toString());
        return executeDatabaseStatements(metadataParams,tableSqls);
    }
   //删除表
   @Override
    public String dropAuditTable(AuditParams auditParams) throws Exception {
        MetadataParams metadataParams = new MetadataParams();
        metadataParams.setDatabaseIP(auditParams.getDatabaseIP());
        metadataParams.setDatabaseName(auditParams.getDatabaseName());
        metadataParams.setPassword(auditParams.getPassword());
        metadataParams.setUserName(auditParams.getUserName());
        metadataParams.setPort(auditParams.getPort());
        //删除表语句
        String dropSql = "drop table `"+auditParams.getTableName()+"` ";
        List<String> dropSqls = new ArrayList<>();
        dropSqls.add(dropSql.toLowerCase());
        return executeDatabaseStatements(metadataParams,dropSqls);
    }


    /**
     * 检测字段名是否重复
     * @param metadataElements
     * @return
     */
    private String checkElementRepeat(List<MetadataElement> metadataElements) {
        Map<String,Object> chekMap = new HashMap<>();
        for (MetadataElement element: metadataElements) {
            if (chekMap.containsKey(element.getFieldName().toLowerCase())){
                return "字段 "+element.getFieldName()+" 重复";
            }
            chekMap.put(element.getFieldName(),element.getFieldName());
        }
        return "";
    }

    /**
     * 字段类型存在以下类型，长度为空时，长度默认值为255
     *
     * @param fieldType
     * @return
     */
    private int getTypelength(String fieldType){
        int length = 0;
        switch (fieldType.toLowerCase()){
            case "int":
                length=11;
                break;
            case "integer":
                length=11;
                break;
            case "long":
                length=11;
                break;
            case "double":
                length=11;
                break;
            case "float":
                length=11;
                break;
            case "decimal":
                length=11;
                break;
            case "varchar":
                length=255;
                break;
            case "blob":
                length=255;
                break;
            case "text":
                length=255;
                break;
        }
        return  length;
    }

	@Override
	public List<Map<String, Object>> selectDataCalogau() {
		return basicMapper.selectDataCalogau();
	}

    @Override
    public PageInfo getTableClassList(Integer pageNum, Integer pageSize, String directory,String notes,String grade) {

        PageHelper.startPage(pageNum,pageSize);
        Map<String,Object> paraMap = new HashMap<>();
        paraMap.put("directory",directory);
        paraMap.put("notes",notes);
        paraMap.put("grade",grade);
        List<Map<String,Object>> tableClass=  basicMapper.getTableClassList(paraMap);
        PageInfo pageInfo = new PageInfo(tableClass);
        return  pageInfo;
    }
}



